package br.com.atibrasil.fx.controller;

import java.awt.Desktop;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.IOException;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.ResourceBundle;

import br.com.atibrasil.util.AlertDialog;
import javafx.application.Platform;
import javafx.beans.value.ChangeListener;
import javafx.beans.value.ObservableValue;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.concurrent.Task;
import javafx.concurrent.Worker;
import javafx.event.Event;
import javafx.event.EventHandler;
import javafx.fxml.FXML;
import javafx.geometry.Insets;
import javafx.geometry.Pos;
import javafx.scene.Group;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.control.ListView;
import javafx.scene.control.ProgressBar;
import javafx.scene.control.ProgressIndicator;
import javafx.scene.control.Tab;
import javafx.scene.control.TabPane;
import javafx.scene.control.TextField;
import javafx.scene.layout.AnchorPane;
import javafx.scene.layout.HBox;
import javafx.scene.layout.VBox;
import javafx.scene.layout.VBoxBuilder;
import javafx.scene.text.Text;
import javafx.stage.FileChooser;
import javafx.stage.FileChooser.ExtensionFilter;
import javafx.stage.Modality;
import javafx.stage.Stage;

public class ImportarForm {

    @FXML
    private ResourceBundle resources;

    @FXML
    private URL location;

    @FXML
    private TextField txtId;

    @FXML
    private Button btnSalvar;
    
    @FXML
    private Button btnChooser;

    @FXML
    private AnchorPane root;

    @FXML
    private Tab tabCadastro;

    @FXML
    private TabPane tabPane;

    @FXML
    private TextField txtPatrimonio;

    @FXML
    private Label status;   
    
    private FileChooser fileChooser = new FileChooser();
    
    @FXML
    private ProgressBar progressBar;   
    

    @FXML
    private ProgressIndicator progressIndicator;

    @SuppressWarnings("unchecked")
	@FXML
    void initialize() {
        assert txtId != null : "fx:id=\"txtId\" was not injected: check your FXML file 'importarForm.fxml'.";
        assert btnSalvar != null : "fx:id=\"btnSalvar\" was not injected: check your FXML file 'importarForm.fxml'.";
        assert root != null : "fx:id=\"root\" was not injected: check your FXML file 'importarForm.fxml'.";
        assert tabCadastro != null : "fx:id=\"tabCadastro\" was not injected: check your FXML file 'importarForm.fxml'.";
        assert tabPane != null : "fx:id=\"tabPane\" was not injected: check your FXML file 'importarForm.fxml'.";
        assert txtPatrimonio != null : "fx:id=\"txtPatrimonio\" was not injected: check your FXML file 'importarForm.fxml'.";
        assert status != null : "fx:id=\"status\" was not injected: check your FXML file 'importarForm.fxml'.";        
        final Desktop desktop = Desktop.getDesktop();              
		
          btnSalvar.setOnAction(new EventHandler(){
			@Override
			public void handle(Event arg0) {
				try {
					  Date inicio = new Date();
					  Connection con;
					  Statement stm;			    	
					  String nome = txtPatrimonio.getText().replace("//", "////");			  
				      FileReader arq = new FileReader(nome);
				      BufferedReader lerArq = new BufferedReader(arq);
				      String linha; 
				      String[] campos = new String[50];
				      String url ="jdbc:postgresql://localhost/ATIBRASIL"; 
				      String usuario="postgres"; 
				      String senha = "root"; 	      	      
				      Class.forName("org.postgresql.Driver"); 
					  con = DriverManager.getConnection(url,usuario,senha);
					  con.setAutoCommit(false);
					  stm = con.createStatement();	
				      stm.executeUpdate("delete from DADOS");
				      con.commit();
				      
					  int i = 1;
					  String insertTableSQL = "INSERT INTO DADOS VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
					  PreparedStatement preparedStatement = con.prepareStatement(insertTableSQL);
					  int linhaArquivo = 1;
		        	  int pai = 0;
				      do {
				        linha = lerArq.readLine();
				        if (linha != null) {
				        	linha = linha.substring(1, linha.length());
				        	int strInicio = 0;	        		
				        	int strFim = linha.indexOf("|");
				        	int j = 1;	      
			        		preparedStatement.setInt(j, i);
			        		j++;
			        		String registro = "";
				        	while (strFim >= 0) {
				        		String campo = linha.substring(strInicio, strFim);
				        		if (j == 2){
					        		registro = campo;
				        			if (campo.equals("C100") || campo.equals("D100")) {
				        				pai = i;
				        			}
				        		}				        						        		
				        		preparedStatement.setString( j , campo);
				        		linha = linha.substring(strFim + 1, linha.length());
				        		strFim = linha.indexOf("|");
				        		j++;
							}		
				        	for (int k = j; k <= 41; k++) {
				        		preparedStatement.setString( k , "");
							}
				        	if (registro.equals("C100") || registro.equals("D100")) {
				        		preparedStatement.setInt( 42 , 0);				        		
				        	}else if (registro.equals("C170") || registro.equals("C190") || registro.equals("D190") ){
				        		preparedStatement.setInt( 42 , pai);
				        	}else{
				        		preparedStatement.setInt( 42 , 0);
				        	}
				        	preparedStatement.setInt( 43 , linhaArquivo);
				        	i++;
				        	linhaArquivo++;
				        	final int x = i;
				        	if (i % 10000 == 0) {
						    	  status.setText(x + " registros importados");
					        	  System.out.println(x + " registros importados");	
				        	}	
				        	preparedStatement.executeUpdate();					        	
				        }
				      } while (linha != null);	  
				      con.commit();
				      status.setText(i + " registros importados");
				      System.out.println(i + " registros importados");
				      con.close();
				      arq.close();
					  stm.close();
					  System.out.println(inicio);
					  System.out.println(new Date());		  					  
				    } catch (IOException | ClassNotFoundException | SQLException e) {
				          System.out.println(e.getMessage());
				          status.setText(e.getMessage());
				    } catch (Exception e) {
				          System.out.println(e.getMessage());
				          status.setText(e.getMessage());
				    }				
			}									
		});
        
        btnChooser.setOnAction(new EventHandler(){
			@Override
			public void handle(Event arg0) {
				fileChooser.setTitle("Importar...");
				fileChooser.getExtensionFilters().setAll(new ExtensionFilter("Arquivo Texto", "*.TXT"));
				 File file = fileChooser.showOpenDialog(new Stage());				
                 if (file != null) {
                	 btnSalvar.setDisable(false);
                     txtPatrimonio.setText(file.toString());
                     //progressBar.setVisible(true);
                 }else{
                	 progressBar.setVisible(false);
                	 btnSalvar.setDisable(true);
                 }
//               final ListView<String> peopleView = new ListView<String>();
//                 final Task task = new Task<ObservableList<String>>() {
//                     @Override protected ObservableList<String> call() throws InterruptedException {
//                   	  for (int i = 0; i < 10; i++) {
//           	        	  //Thread.sleep(200);
//           	        	  //updateProgress(i+1, 10);
//                   	  }
//                   	  return FXCollections.observableArrayList("John", "Jim", "Geoff", "Jill", "Suki");
//                     }
//                   };
//                   progressBar.progressProperty().bind(task.progressProperty());
//                   task.stateProperty().addListener(new ChangeListener<Worker.State>() {
//                     @Override public void changed(ObservableValue<? extends Worker.State> observableValue, Worker.State oldState, Worker.State newState) {
//                       if (newState == Worker.State.SUCCEEDED) {
//                         System.out.println("This is ok, this thread " + Thread.currentThread() + " is the JavaFX Application thread.");
//                         Platform.runLater(new Runnable(){
//                             @Override
//                             public void run() {
//                                 btnSalvar.fire();
//                             }
//                         });
//                       }
//                     }
//                   });                                      
//                   new Thread(task).start();          
			}
		});                            
    }       
}
